package com.xm.dao.background;

import com.xm.entity.*;
import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import org.intellij.lang.annotations.Language;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 订单后台
 *
 * @author 康文程
 * @date 2024-1-9 下午 03:53
 */
public class OrdersDao extends BaseUtil {
    /**
     * 分页查询所有订单，并关联查询address表
     *
     * @param pageNo   页码
     * @param pageSize 每页数量
     * @param sort     排序方式
     * @param state    订单状态
     * @param nameOrId 地址名称或订单ID
     * @return 分页结果
     * @throws SqlErrorException SQL执行异常
     */
    public Page<Orders> getAllOrdersWithAddressByPage(int pageNo, int pageSize, String sort, String state, String nameOrId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT o.*, a.* FROM orders o " +
                "INNER JOIN address a ON o.address_id = a.address_id " +
                "WHERE 1=1";
        List<Object> params = new ArrayList<>();
        if (nameOrId != null) {
            sql += " AND (a.address_name LIKE ? OR o.orders_id = ?)";
            params.add("%" + nameOrId + "%");
            params.add(nameOrId);}
        int shipmentsStatus = 4;int logistics = 10;
        if (Integer.parseInt(state) == shipmentsStatus
                || Integer.parseInt(state) == 6 || Integer.parseInt(state) == 7
                || Integer.parseInt(state) == 8 || Integer.parseInt(state) == 9) {
            sql += " AND (o.orders_status = ?)";
            params.add(state);
        } else if (Integer.parseInt(state) == logistics) {
            sql += " AND (o.orders_status = 6 or o.orders_status = 7 or o.orders_status = 8 or o.orders_status = 9)";
        } else {
            sql += " AND (o.orders_status = ? and order_expiration_time = NOW())";
            params.add(state);
        }
        sql += " ORDER BY o.place_order_date " + sort;
        sql += " LIMIT ?, ?";
        int offset = (pageNo - 1) * pageSize;
        ResultSet resultSet = null;
        try {
            params.add(offset);
            params.add(pageSize);
            resultSet = executeQuery(sql, params.toArray());
            List<Orders> orderList = new ArrayList<>();
            while (resultSet.next()) {
                Orders order = new Orders();
                order.setOrdersId(resultSet.getString("orders_id"));
                order.setPaymentPrice(resultSet.getBigDecimal("payment_price"));
                order.setCouponCredit(resultSet.getBigDecimal("coupon_credit"));
                order.setTotalMerchandise(resultSet.getInt("total_merchandise"));
                order.setEstimatedTimeArrival(resultSet.getTimestamp("estimated_time_arrival"));
                order.setModeOfDelivery(resultSet.getInt("mode_of_delivery"));
                order.setShipmentStatus(resultSet.getInt("shipment_status"));
                order.setOrdersStatus(resultSet.getInt("orders_status"));
                order.setPlaceOrderDate(resultSet.getTimestamp("place_order_date"));
                order.setOrderExpirationTime(resultSet.getTimestamp("order_expiration_time"));
                order.setPaymentMethods(resultSet.getInt("payment_methods"));
                order.setCommodityIds(resultSet.getString("commodity_ids"));
                order.setAddressId(resultSet.getInt("address_id"));
                order.setLogisticsStationId(resultSet.getInt("logistics_station_id"));
                order.setSpecificationIds(resultSet.getString("specification_ids"));
                order.setInsuranceIds(resultSet.getString("insurance_ids"));
                order.setWarehouseId(resultSet.getInt("warehouse_id"));
                order.setLogisticsCompaniesId(resultSet.getInt("logistics_companies_id"));
                order.setFreight(resultSet.getBigDecimal("freight"));
                Address address = new Address();
                address.setAddressId(resultSet.getInt("address_id"));
                address.setAddressName(resultSet.getString("address_name"));
                address.setAddressPhone(resultSet.getString("address_phone"));
                address.setAddressProvince(resultSet.getString("address_province"));
                address.setAddressCity(resultSet.getString("address_city"));
                address.setAddressDistrict(resultSet.getString("address_district"));
                address.setFullAddress(resultSet.getString("full_address"));
                address.setAddressLabels(resultSet.getString("address_labels"));
                address.setIsSelectedAddress(resultSet.getInt("is_selected_address"));
                address.setInformationId(resultSet.getInt("information_id"));
                address.setLongitude(resultSet.getString("longitude"));
                address.setLatitude(resultSet.getString("latitude"));
                order.setAddress(address);
                orderList.add(order);
            }
            Page<Orders> page = new Page<>();
            page.setData(orderList);
            page.setPageNumber(pageNo);
            page.setPageSize(pageSize);
            page.setCount(getTotalOrderCount(nameOrId, state));
            page.setTotalPages(pageSize);
            return page;
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
    }

    /**
     * 分页查询所有订单，并关联查询address表
     *
     * @param pageNo   页码
     * @param pageSize 每页数量
     * @param sort     排序方式
     * @param state    订单状态
     * @param nameOrId 地址名称或订单ID
     * @return 分页结果
     * @throws SqlErrorException SQL执行异常
     */
    public Page<Orders> getAllOrdersByPage(int pageNo, int pageSize, String sort, String state, String nameOrId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT o.*, a.*, c.* FROM orders o " +
                "INNER JOIN address a ON o.address_id = a.address_id " +
                "LEFT JOIN coupons c ON o.coupons_id = c.coupon_id " +
                "WHERE 1=1";
        List<Object> params = new ArrayList<>();
        if (nameOrId != null) {
            sql += " AND (a.address_name LIKE ? OR o.orders_id = ?)";
            params.add("%" + nameOrId + "%");
            params.add(nameOrId);}
        int shipmentsStatus = 4;int logistics = 10;
        if (Integer.parseInt(state) == shipmentsStatus
                || Integer.parseInt(state) == 6 || Integer.parseInt(state) == 7
                || Integer.parseInt(state) == 8 || Integer.parseInt(state) == 9) {
            sql += " AND (o.orders_status = ?)";
            params.add(state);
        } else if (Integer.parseInt(state) == logistics) {
            sql += " AND (o.orders_status = 6 or o.orders_status = 7 or o.orders_status = 8 or o.orders_status = 9)";
        } else {
            sql += " AND (o.orders_status = ? and order_expiration_time = NOW())";
            params.add(state);
        }
        sql += " ORDER BY o.place_order_date " + sort;
        sql += " LIMIT ?, ?";
        int offset = (pageNo - 1) * pageSize;
        ResultSet resultSet = null;
        try {
            params.add(offset);
            params.add(pageSize);
            resultSet = executeQuery(sql, params.toArray());
            List<Orders> orderList = new ArrayList<>();
            while (resultSet.next()) {
                Orders order = new Orders();
                order.setOrdersId(resultSet.getString("orders_id"));
                order.setPaymentPrice(resultSet.getBigDecimal("payment_price"));
                order.setCouponCredit(resultSet.getBigDecimal("coupon_credit"));
                order.setTotalMerchandise(resultSet.getInt("total_merchandise"));
                order.setEstimatedTimeArrival(resultSet.getTimestamp("estimated_time_arrival"));
                order.setModeOfDelivery(resultSet.getInt("mode_of_delivery"));
                order.setShipmentStatus(resultSet.getInt("shipment_status"));
                order.setOrdersStatus(resultSet.getInt("orders_status"));
                order.setPlaceOrderDate(resultSet.getTimestamp("place_order_date"));
                order.setOrderExpirationTime(resultSet.getTimestamp("order_expiration_time"));
                order.setPaymentMethods(resultSet.getInt("payment_methods"));
                order.setCommodityIds(resultSet.getString("commodity_ids"));
                order.setAddressId(resultSet.getInt("address_id"));
                order.setLogisticsStationId(resultSet.getInt("logistics_station_id"));
                order.setSpecificationIds(resultSet.getString("specification_ids"));
                order.setInsuranceIds(resultSet.getString("insurance_ids"));
                order.setWarehouseId(resultSet.getInt("warehouse_id"));
                order.setLogisticsCompaniesId(resultSet.getInt("logistics_companies_id"));
                order.setFreight(resultSet.getBigDecimal("freight"));
                Address address = new Address();
                address.setAddressId(resultSet.getInt("address_id"));
                address.setAddressName(resultSet.getString("address_name"));
                address.setAddressPhone(resultSet.getString("address_phone"));
                address.setAddressProvince(resultSet.getString("address_province"));
                address.setAddressCity(resultSet.getString("address_city"));
                address.setAddressDistrict(resultSet.getString("address_district"));
                address.setFullAddress(resultSet.getString("full_address"));
                address.setAddressLabels(resultSet.getString("address_labels"));
                address.setIsSelectedAddress(resultSet.getInt("is_selected_address"));
                address.setInformationId(resultSet.getInt("information_id"));
                address.setLongitude(resultSet.getString("longitude"));
                address.setLatitude(resultSet.getString("latitude"));
                order.setAddress(address);
                orderList.add(order);
            }
            Page<Orders> page = new Page<>();
            page.setData(orderList);
            page.setPageNumber(pageNo);
            page.setPageSize(pageSize);
            page.setCount(getTotalOrderCount(nameOrId, state));
            page.setTotalPages(pageSize);
            return page;
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
    }
    /**
     * 根据订单ID查询订单
     *
     * @param orderId 订单ID
     * @return 订单对象
     * @throws SqlErrorException SQL执行异常
     */
    public Orders getOrderByOrderId(String orderId) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT o.*, a.*, c.*,lc.logistics_companies_name,ls.*,w.* " +
                "FROM orders o " +
                "LEFT JOIN address a " +
                "ON o.address_id = a.address_id " +
                "LEFT JOIN coupons c " +
                "ON o.coupons_id = c.coupon_id " +
                "left join logistics_companies lc " +
                "on o.logistics_companies_id = lc.logistics_companies_id " +
                "left join logistics_station ls " +
                "on o.logistics_companies_id = ls.logistics_companies_id " +
                "left join warehouse w " +
                "on o.warehouse_id = w.warehouse_id " +
                "WHERE o.orders_id = ?";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql, orderId);
            if (resultSet.next()) {
                Orders order = new Orders();
                order.setOrdersId(resultSet.getString("orders_id"));
                order.setPaymentPrice(resultSet.getBigDecimal("payment_price"));
                order.setCouponCredit(resultSet.getBigDecimal("coupon_credit"));
                order.setTotalMerchandise(resultSet.getInt("total_merchandise"));
                order.setEstimatedTimeArrival(resultSet.getTimestamp("estimated_time_arrival"));
                order.setModeOfDelivery(resultSet.getInt("mode_of_delivery"));
                order.setShipmentStatus(resultSet.getInt("shipment_status"));
                order.setOrdersStatus(resultSet.getInt("orders_status"));
                order.setPlaceOrderDate(resultSet.getTimestamp("place_order_date"));
                order.setOrderExpirationTime(resultSet.getTimestamp("order_expiration_time"));
                order.setPaymentMethods(resultSet.getInt("payment_methods"));
                order.setCommodityIds(resultSet.getString("commodity_ids"));
                order.setAddressId(resultSet.getInt("address_id"));
                order.setLogisticsStationId(resultSet.getInt("logistics_station_id"));
                order.setSpecificationIds(resultSet.getString("specification_ids"));
                order.setInsuranceIds(resultSet.getString("insurance_ids"));
                order.setWarehouseId(resultSet.getInt("warehouse_id"));
                order.setLogisticsCompaniesId(resultSet.getInt("logistics_companies_id"));
                order.setFreight(resultSet.getBigDecimal("freight"));
                order.setNumberProducts(resultSet.getString("number_products"));

                Address address = new Address();
                address.setAddressId(resultSet.getInt("address_id"));
                address.setAddressName(resultSet.getString("address_name"));
                address.setAddressPhone(resultSet.getString("address_phone"));
                address.setAddressProvince(resultSet.getString("address_province"));
                address.setAddressCity(resultSet.getString("address_city"));
                address.setAddressDistrict(resultSet.getString("address_district"));
                address.setFullAddress(resultSet.getString("full_address"));
                address.setAddressLabels(resultSet.getString("address_labels"));
                address.setIsSelectedAddress(resultSet.getInt("is_selected_address"));
                address.setInformationId(resultSet.getInt("information_id"));
                address.setLongitude(resultSet.getString("longitude"));
                address.setLatitude(resultSet.getString("latitude"));

                order.setAddress(address);

                Coupons coupons = new Coupons();
                coupons.setCouponId(resultSet.getInt("coupon_id"));
                coupons.setCouponTitle(resultSet.getString("coupon_title"));
                coupons.setCouponRange(resultSet.getString("coupon_range"));
                coupons.setCouponValidTime(resultSet.getTimestamp("coupon_valid_time"));
                coupons.setCouponEndTime(resultSet.getTimestamp("coupon_end_time"));
                coupons.setCouponStatus(resultSet.getInt("coupon_status"));
                coupons.setInformationId(resultSet.getInt("information_id"));
                coupons.setDiscount(resultSet.getBigDecimal("discount"));

                LogisticsCompanies logisticsCompanies = new LogisticsCompanies();
                logisticsCompanies.setLogisticsCompaniesName(resultSet.getString("logistics_companies_name"));
                order.setLogisticsCompanies(logisticsCompanies);
                LogisticsStation logisticsStation = new LogisticsStation();
                logisticsStation.setLogisticsStationName(resultSet.getString("logistics_station_name"));
                logisticsStation.setLogisticsStationDeliveryman(resultSet.getString("logistics_station_deliveryman"));
                logisticsStation.setLogisticsStationProvince(resultSet.getString("logistics_station_province"));
                logisticsStation.setLogisticsStationCity(resultSet.getString("logistics_station_city"));
                logisticsStation.setLogisticsStationDistrict(resultSet.getString("logistics_station_district"));
                logisticsStation.setLogisticsStationPhone(resultSet.getString("logistics_station_phone"));
                logisticsStation.setLogisticsStationLongitude(resultSet.getString("logistics_station_longitude"));
                logisticsStation.setLogisticsStationLatitude(resultSet.getString("logistics_station_latitude"));
                order.setLogisticsStation(logisticsStation);

                Warehouse warehouse = new Warehouse();
                warehouse.setWarehouseName(resultSet.getString("warehouse_name"));
                warehouse.setWarehouseLongitude(resultSet.getString("warehouse_longitude"));
                warehouse.setWarehouseLatitude(resultSet.getString("warehouse_latitude"));
                order.setWarehouse(warehouse);
                order.setCoupons(coupons);

                return order;

            } else {
                return null;
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
    }

    /**
     * 获取订单总数
     *
     * @return 订单总数
     * @throws SqlErrorException SQL执行异常
     */
    private int getTotalOrderCount(String nameOrId, String state) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT COUNT(*) FROM orders o " +
                "INNER JOIN address a ON o.address_id = a.address_id " +
                "where 1 = ? ";
        List<Object> params = new ArrayList<>();
        params.add(1);
        if (nameOrId != null) {
            sql += " AND (a.address_name LIKE ? OR o.orders_id = ?)";
            params.add("%" + nameOrId + "%");
            params.add(nameOrId);
        }
        int shipmentsStatus = 4;
        int logistics = 10;
        if (Integer.parseInt(state) == shipmentsStatus
                || Integer.parseInt(state) == 6
                || Integer.parseInt(state) == 7
                || Integer.parseInt(state) == 8
                || Integer.parseInt(state) == 9) {
            sql += " AND (o.orders_status = ?)";
            params.add(state);
        } else if (Integer.parseInt(state) == logistics) {
            sql += " AND (o.orders_status = 6 or o.orders_status = 7 or o.orders_status = 8 or o.orders_status = 9)";
        } else {
            sql += " AND (o.orders_status = ? and order_expiration_time = NOW())";
            params.add(state);
        }

        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql, params.toArray());
            if (resultSet.next()) {
                return resultSet.getInt(1);
            } else {
                return 0;
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
    }

    /**
     * 根据订单Id修改logistics_companies_id、logistics_station_id、orders_status、mode_of_delivery
     *
     * @param orderId              订单ID
     * @param logisticsCompaniesId 物流公司ID
     * @param logisticsStationId   物流站点ID
     * @param ordersStatus         订单状态
     * @param modeOfDelivery       配送方式
     * @return 是否修改成功
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateOrderDetails(String orderId, int logisticsCompaniesId, int logisticsStationId, int ordersStatus, int modeOfDelivery) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE orders SET logistics_companies_id = ?, logistics_station_id = ?, orders_status = ?, mode_of_delivery = ? WHERE orders_id = ?";
        try {
            int rowsAffected = executeUpdate(sql, logisticsCompaniesId, logisticsStationId, ordersStatus, modeOfDelivery, orderId);
            return rowsAffected > 0;
        } catch (Exception e) {
            throw new SqlErrorException("修改失败", e);
        }
    }

    /**
     * 根据订单编号修改warehouse_id
     *
     * @param orderId     订单编号
     * @param warehouseId 仓库编号
     * @return 是否修改成功
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateWarehouseIdByOrderId(String orderId, int warehouseId) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE orders SET warehouse_id = ? WHERE orders_id = ?";
        try {
            int rowsAffected = executeUpdate(sql, warehouseId, orderId);
            return rowsAffected > 0;
        } catch (Exception e) {
            throw new SqlErrorException("修改失败", e);
        }
    }
    /**
     * 根据订单ID修改订单状态
     *
     * @param orderId      订单ID
     * @param ordersStatus 订单状态
     * @return 是否修改成功
     * @throws SqlErrorException SQL执行异常
     */
    public boolean updateOrderStatusByOrderId(String orderId, Integer ordersStatus) throws SqlErrorException {
        @Language("sql")
        String sql = "UPDATE orders SET orders_status = ? WHERE orders_id = ?";
        try {
            int rowsAffected = executeUpdate(sql, ordersStatus, orderId);
            return rowsAffected > 0;
        } catch (Exception e) {
            throw new SqlErrorException("修改失败", e);
        }
    }
    /**
     * 查询orders_status为9的订单数量
     * @param status 订单状态
     * @return 订单数量
     * @throws SqlErrorException SQL执行异常
     */
    public int getOrdersCountByStatusNine(Integer status) throws SqlErrorException {
        @Language("sql")
        String sql = "SELECT COUNT(*) FROM orders WHERE orders_status = ?";
        ResultSet resultSet = null;
        try {
            resultSet = executeQuery(sql,status);
            if (resultSet.next()) {
                return resultSet.getInt(1);
            } else {
                return 0;
            }
        } catch (Exception e) {
            throw new SqlErrorException("查询失败", e);
        } finally {
            close(resultSet);
        }
    }
}
